package mypackage.dao.impl;

import mypackage.bean.Comments;
import mypackage.dao.CommentsDao;
import mypackage.utils.JDBCUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

/**
 * @author YH925
 * @create 2021/12/11 13:45
 */
public class CommentsDaoImpl implements CommentsDao {
    Connection connection;

    {
        try {
            connection = JDBCUtils.getConnection();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    QueryRunner runner = new QueryRunner();
    @Override
    public List<Comments> selectByGoodsId(String id) {
        String sql = "select * from comments where goodsId = ?";
        BeanListHandler<Comments>handler = new BeanListHandler<>(Comments.class);
        List<Comments> comments = null;
        try {
            comments = runner.query(connection, sql, handler, id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return comments;
    }

    /**
     * 用户对某件商品写评论
     * @param username
     * @param stars
     * @param comments
     * @param goodsId
     */
    @Override
    public void insertCommentsOnTable(String username, int stars, String comments, String goodsId) {
        try {
            connection.setAutoCommit(false);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        String sql = "insert into comments values(?,?,?,to_date(?,'YYYY-MM-DD HH24:MI:SS'),?,?)";

        Date date = new Date();
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm;ss");
        String time = dateFormat.format(date);

        try {
            runner.update(connection,sql,null,username,stars,time,comments,goodsId);
            connection.commit();
        } catch (SQLException e) {
            try {
                connection.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            e.printStackTrace();
        }

    }

    @Override
    public int findAllCommentsByGoodId(String id) {
        String sql = "select count(*) from comments where goodsid = ?";
        ScalarHandler<Object>handler = new ScalarHandler<>();
        Object obj = null;
        try {
            obj = runner.query(connection,sql,handler,id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        Integer count = Integer.parseInt(obj.toString());
        return count;
    }

    @Override
    public int findAllGoodCommentsById(String id) {
        String sql = "select count(*) from comments where goodsid = ? and stars = ?";
        ScalarHandler<Object>handler = new ScalarHandler<>();
        Object obj = null;
        try {
            obj = runner.query(connection,sql,handler,id,3);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        Integer count = Integer.parseInt(obj.toString());
        return count;
    }

    @Override
    public int findAllPlainCommentsById(String id) {
        String sql = "select count(*) from comments where goodsid = ? and stars = ?";
        ScalarHandler<Object>handler = new ScalarHandler<>();
        Object obj = null;
        try {
            obj = runner.query(connection,sql,handler,id,2);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        Integer count = Integer.parseInt(obj.toString());
        return count;
    }

    @Override
    public int findAllBadCommentsById(String id) {
        String sql = "select count(*) from comments where goodsid = ? and stars = ?";
        ScalarHandler<Object>handler = new ScalarHandler<>();
        Object obj = null;
        try {
            obj = runner.query(connection,sql,handler,id,1);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        Integer count = Integer.parseInt(obj.toString());
        return count;
    }


}
